package com.hebust.mapper;

import com.hebust.utils.admin.*;
import org.apache.ibatis.annotations.*;

import java.util.List;


// 本接口  是 管理员登录以后  所有功能实现的接口
@Mapper
public interface AdminMapper {

    // 学院管理的增删查改
    @Select("SELECT DISTINCT \n" +
            "d_id as id,\n" +
            "d_name as name,\n" +
            "(select count(*) from major where d_id = m_department_id) as \"mNum\",\n" +
            "(select count(*) from teachers where d_id = t_department_id) as \"tNum\"\n" +
            "\n" +
            "from department,major,teachers\n" +
            "\n" +
            "where d_name LIKE concat('%',#{name},'%')")
    List<AdminDepartment> show_department(@Param("name") String name);

    @Insert("INSERT INTO department(d_name) VALUES(#{name})")
    Integer add_department(@Param("name") String name);


    @Update("update department set d_name=#{name} where d_id=#{id}")
    Integer edit_department(@Param("id") int id, @Param("name") String name);

    @Delete("DELETE from department where d_id=#{id}")
    Integer delete_department(@Param("id") int id);

    // 专业管理的增删查改
    @Select("SELECT \n" +
            "m_id as id,\n" +
            "m_name as name,\n" +
            "d_name as dName\n" +
            "from major,department\n" +
            "where m_department_id = d_id \n" +
            "and m_name like concat('%',#{name},'%')\n")
    List<AdminProcessional> show_processional(@Param("name") String name);

    @Insert("INSERT INTO major(m_department_id,m_name) VALUES(#{id},#{name})")
    Integer add_processional(@Param("id") int id, @Param("name") String name);

    @Select("SELECT\n" +
            "d_id as id,\n" +
            "d_name as `name`\n" +
            "FROM\n" +
            "department")
    List<AdminProcessionalDialog> search();

    @Update("update major\n" +
            "set m_department_id = #{region},\n" +
            "m_name = #{name}\n" +
            "where m_id = #{id}")
    Integer edit_processional(@Param("id") int id, @Param("name") String name, @Param("region") int region);

    @Delete("DELETE from major\n" +
            "where m_id = #{id}")
    Integer delete_processional(@Param("id") int id);


    // 班级管理的增删查改

    @Select("select \n" +
            "c_id as id,\n" +
            "c_name as name,\n" +
            "c_grade as grade,\n" +
            "d_name as dName,\n" +
            "m_name as mName\n" +
            "from class,major,department\n" +
            "where c_major_id = m_id \n" +
            "and m_department_id = d_id\n" +
            "and c_name like CONCAT('%', #{name} ,'%')")
    List<AdminClass> show_class(@Param("name") String name);

    @Select("SELECT\n" +
            "m_id as id,\n" +
            "m_name as `name`\n" +
            "\n" +
            "from major")
    List<AdminClassDialog> search_class();

    @Insert("INSERT INTO class(c_major_id,c_name,c_grade) VALUES(#{id},#{name},#{grade})")
    Integer add_class(@Param("id") int id, @Param("name") String name, @Param("grade") int grade);

    @Update("update class set c_major_id = #{region}, c_grade = #{grade},c_name = #{name}\n" +
            "where c_id = #{id}")
    Integer edit_class(@Param("id") int id, @Param("name") String name, @Param("grade") int grade, @Param("region") int region);

    @Delete("DELETE from class\n" +
            "where c_id = #{id}")
    Integer delete_class(@Param("id") int id);


    // 学生管理的增删查改

    @Select("SELECT\n" +
            "s_id as id,\n" +
            "s_number as num,\n" +
            "s_name as name,\n" +
            "s_password as password,\n" +
            "c_name as clazz,\n" +
            "m_name as major,\n" +
            "s_sex as sex\n" +
            "from students,class,major\n" +
            "WHERE s_class_id = c_id \n" +
            "and c_major_id = m_id\n" +
            "and s_name like CONCAT('%', #{name} ,'%')")
    List<AdminStudent> show_student(@Param("name") String name);

    @Select("SELECT\n" +
            "c_id as id,\n" +
            "c_name as name\n" +
            "from class")
    List<AdminStudentDialog> search_student();

    @Insert("insert into students(s_class_id,s_number,s_name,s_password,s_sex) values(#{id},#{num},#{name},#{password},#{sex})")
    Integer add_student(@Param("id") int id, @Param("name") String name, @Param("num") String num, @Param("sex") String sex, @Param("password") String password);

    @Update("update students \n" +
            "set s_class_id = #{cid},\n" +
            "s_number = #{num},\n" +
            "s_name = #{name},\n" +
            "s_password = #{password},\n" +
            "s_sex = #{sex}\n" +
            "where s_id = #{id}")
    Integer edit_student(@Param("id") int id, @Param("cid") int cid, @Param("name") String name, @Param("num") String num, @Param("sex") String sex, @Param("password") String password);

    @Delete("DELETE from students\n" +
            "where s_id = #{id}")
    Integer delete_student(@Param("id") int id);

    // 教师管理的增删改查

    @Select("SELECT\n" +
            "t_id as id,\n" +
            "t_number as num,\n" +
            "t_name as name,\n" +
            "t_password as password,\n" +
            "d_name as department\n" +
            "from teachers,department\n" +
            "where t_department_id = d_id\n" +
            "and t_name like CONCAT('%',#{name},'%')")
    List<AdminTeacher> show_teacher(@Param("name") String name);

    @Select("SELECT\n" +
            "d_id as id,\n" +
            "d_name as name\n" +
            "from department")
    List<AdminTeacherDialog> search_teacher();

    @Insert("INSERT INTO teachers(t_department_id,t_number,t_name,t_password) VALUES(#{id},#{num},#{name},#{password});")
    Integer add_teacher(@Param("id") int id, @Param("name") String name, @Param("num") String num, @Param("password") String password);

    @Update("UPDATE teachers set t_department_id = #{did},\n" +
            "t_number = #{num},\n" +
            "t_password = #{password},\n" +
            "t_name = #{name}\n" +
            "where t_id = #{id}")
    Integer edit_teacher(@Param("id") int id, @Param("num") String num, @Param("name") String name, @Param("password") String password, @Param("did") int did);

    @Delete("DELETE from teachers\n" +
            "where t_id = #{id}")
    Integer delete_teacher(@Param("id") int id);

    // 排课管理的增删改查


    @Select("SELECT \n" +
            "co_id as id,\n" +
            "co_name as cname,\n" +
            "t_name as tname,\n" +
            "d_name as department,\n" +
            "co_grade as grade,\n" +
            "co_credit as credit,\n" +
            "co_time as ttime,\n" +
            "co_location as location,\n" +
            "co_select_count as sselect,\n" +
            "co_max_size as max\n" +
            "from course,teachers,department\n" +
            "where co_teacher_id = t_id \n" +
            "and t_department_id = d_id\n" +
            "and co_name like CONCAT('%',#{name},'%')")
    List<AdminCourse> show_course(@Param("name") String name);

    @Select("select t_id as id,\n" +
            "t_name as `name`\n" +
            "from teachers")
    List<AdminCourseDialog> search_course();

    @Insert("INSERT INTO course(co_teacher_id,co_name,co_credit,co_time,co_select_count,co_max_size,co_grade,co_location) VALUES(#{id},#{name},#{credit},#{ttime},#{sselect},#{max},#{grade},#{location})")
    Integer add_course(@Param("id") int id, @Param("name") String name, @Param("credit") int credit, @Param("ttime") String ttime, @Param("sselect") int sselect, @Param("max") int max, @Param("grade") int grade, @Param("location") String location);


    @Update("UPDATE course\n" +
            "SET co_teacher_id = #{tid},\n" +
            "co_name = #{name},\n" +
            "co_credit = #{credit},\n" +
            "co_time = #{ttime},\n" +
            "co_select_count = #{sselect},\n" +
            "co_max_size = #{max},\n" +
            "co_grade = #{grade},\n" +
            "co_location = #{location}\n" +
            "WHERE co_id = #{id}")
    Integer edit_course(@Param("id") int id, @Param("name") String name, @Param("credit") int credit, @Param("ttime") String ttime, @Param("sselect") int sselect, @Param("max") int max, @Param("grade") int grade, @Param("location") String location, @Param("tid") int tid);

    @Delete("delete from course\n" +
            "where co_id =#{id}")
    Integer delete_course(@Param("id") int id);

    // 选课结果的增删改查

    @Select("SELECT\n" +
            "sc_id as id,\n" +
            "co_name as cname,\n" +
            "sc_teacher_name as tname,\n" +
            "s_name as sname,\n" +
            "c_name as cclass,\n" +
            "sc_score as score\n" +
            "from studentscore,students,course,class\n" +
            "where sc_student_id = s_id \n" +
            "and sc_course_id = co_id\n" +
            "and s_class_id = c_id\n" +
            "and co_name like CONCAT('%',#{cname},'%')\n" +
            "and s_name like CONCAT('%',#{sname},'%')")
    List<AdminSelectResult> show_selectresult(@Param("cname") String cname, @Param("sname") String sname);

    @Update("UPDATE studentscore\n" +
            "set sc_score = #{score}\n" +
            "WHERE sc_id = #{id}")
    Integer update_studentscore(@Param("id") int id, @Param("score") int score);

    @Delete("DELETE FROM\n" +
            "studentscore\n" +
            "where sc_id = #{id}")
    Integer delete_selectresult(@Param("id") int id);

}
